"""
态势对象数据同步模块的数据库模型
"""
import uuid
from datetime import datetime
from typing import Optional
from sqlalchemy import Column, String, DateTime, Boolean, Text, Integer, CheckConstraint, Index
from sqlalchemy.dialects.postgresql import UUID
try:
    from sqlalchemy.dialects.postgresql import JSONB
except ImportError:
    # 如果JSONB不可用，使用通用JSON类型
    from sqlalchemy import JSON as JSONB
from src.situation.database import Base


class DataSource(Base):
    """数据源配置表"""

    __tablename__ = "data_sources"

    # 主键和基础信息
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, comment="主键ID")
    name = Column(String(200), nullable=False, comment="数据源名称")
    description = Column(Text, comment="数据源描述")

    # 数据库连接配置
    db_type = Column(String(50), nullable=False, comment="数据库类型: mysql/postgresql")
    host = Column(String(255), nullable=False, comment="数据库主机")
    port = Column(Integer, nullable=False, comment="数据库端口")
    database_name = Column(String(100), nullable=False, comment="数据库名称")
    username = Column(String(100), nullable=False, comment="用户名")
    password = Column(String(500), nullable=False, comment="密码（加密存储）")
    connection_params = Column(JSONB, default=dict, comment="额外连接参数")

    # 连接测试信息
    is_connected = Column(Boolean, default=False, comment="是否已连接测试成功")
    last_test_time = Column(DateTime, comment="最后测试时间")
    test_error = Column(Text, comment="测试错误信息")

    # 审计字段
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False, comment="更新时间")
    created_by = Column(String(100), comment="创建人")
    updated_by = Column(String(100), comment="更新人")

    # 软删除
    is_deleted = Column(Boolean, default=False, nullable=False, index=True, comment="是否已删除")
    deleted_at = Column(DateTime, comment="删除时间")

    # 表约束
    __table_args__ = (
        CheckConstraint("db_type IN ('mysql', 'postgresql')", name="chk_db_type"),
        Index("idx_data_sources_name", "name"),
        Index("idx_data_sources_type", "db_type"),
    )

    def __repr__(self):
        return f"<DataSource(id={self.id}, name={self.name}, type={self.db_type})>"


class FieldMapping(Base):
    """字段映射配置表"""

    __tablename__ = "field_mappings"

    # 主键和基础信息
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, comment="主键ID")
    sync_task_id = Column(UUID(as_uuid=True), nullable=False, comment="同步任务ID")
    source_field = Column(String(200), nullable=False, comment="源字段名称")
    target_field = Column(String(200), nullable=False, comment="目标字段名称")
    field_type = Column(String(100), nullable=False, comment="字段类型")
    is_required = Column(Boolean, default=False, comment="是否必需字段")
    default_value = Column(String(500), comment="默认值")
    transform_rule = Column(Text, comment="转换规则")

    # 审计字段
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False, comment="更新时间")

    # 表约束
    __table_args__ = (
        Index("idx_field_mappings_task", "sync_task_id"),
        Index("idx_field_mappings_source", "source_field"),
        Index("idx_field_mappings_target", "target_field"),
    )

    def __repr__(self):
        return f"<FieldMapping(id={self.id}, source={self.source_field}, target={self.target_field})>"


class SyncTask(Base):
    """数据同步任务表"""

    __tablename__ = "sync_tasks"

    # 主键和基础信息
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, comment="主键ID")
    task_name = Column(String(200), nullable=False, comment="任务名称")
    description = Column(Text, comment="任务描述")

    # 数据源信息
    data_source_id = Column(UUID(as_uuid=True), nullable=False, comment="数据源ID")
    source_table = Column(String(200), nullable=False, comment="源表名称")

    # 同步配置
    sync_mode = Column(String(50), nullable=False, default="manual", comment="同步模式: manual/scheduled")
    schedule_expression = Column(String(200), comment="定时表达式 (cron)")
    batch_size = Column(Integer, default=1000, comment="批次大小")
    sync_filter = Column(Text, comment="同步过滤条件")
    is_active = Column(Boolean, default=True, comment="是否启用")

    # 同步状态
    last_sync_time = Column(DateTime, comment="最后同步时间")
    next_sync_time = Column(DateTime, comment="下次同步时间")
    sync_status = Column(String(50), default="pending", comment="同步状态: pending/running/success/failed")
    total_records = Column(Integer, default=0, comment="总记录数")
    synced_records = Column(Integer, default=0, comment="已同步记录数")
    failed_records = Column(Integer, default=0, comment="失败记录数")

    # 错误信息
    last_error = Column(Text, comment="最后错误信息")

    # 审计字段
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False, comment="更新时间")
    created_by = Column(String(100), comment="创建人")
    updated_by = Column(String(100), comment="更新人")

    # 软删除
    is_deleted = Column(Boolean, default=False, nullable=False, index=True, comment="是否已删除")
    deleted_at = Column(DateTime, comment="删除时间")

    # 表约束
    __table_args__ = (
        CheckConstraint("sync_mode IN ('manual', 'scheduled')", name="chk_sync_mode"),
        CheckConstraint("sync_status IN ('pending', 'running', 'success', 'failed')", name="chk_sync_status"),
        Index("idx_sync_tasks_name", "task_name"),
        Index("idx_sync_tasks_source", "data_source_id"),
        Index("idx_sync_tasks_status", "sync_status"),
        Index("idx_sync_tasks_next_sync", "next_sync_time"),
    )

    def __repr__(self):
        return f"<SyncTask(id={self.id}, name={self.task_name}, status={self.sync_status})>"


class SyncLog(Base):
    """同步日志表"""

    __tablename__ = "sync_logs"

    # 主键和基础信息
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, comment="主键ID")
    sync_task_id = Column(UUID(as_uuid=True), nullable=False, comment="同步任务ID")

    # 执行信息
    execution_id = Column(String(100), nullable=False, comment="执行ID")
    start_time = Column(DateTime, nullable=False, comment="开始时间")
    end_time = Column(DateTime, comment="结束时间")
    duration = Column(Integer, comment="执行时长(秒)")

    # 执行结果
    status = Column(String(50), nullable=False, comment="执行状态: running/success/failed")
    total_records = Column(Integer, default=0, comment="总记录数")
    processed_records = Column(Integer, default=0, comment="处理记录数")
    success_records = Column(Integer, default=0, comment="成功记录数")
    failed_records = Column(Integer, default=0, comment="失败记录数")
    skipped_records = Column(Integer, default=0, comment="跳过记录数")

    # 详细信息
    error_message = Column(Text, comment="错误信息")
    execution_details = Column(JSONB, default=dict, comment="执行详情")

    # 审计字段
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False, comment="创建时间")

    # 表约束
    __table_args__ = (
        CheckConstraint("status IN ('running', 'success', 'failed')", name="chk_log_status"),
        Index("idx_sync_logs_task", "sync_task_id"),
        Index("idx_sync_logs_execution", "execution_id"),
        Index("idx_sync_logs_status", "status"),
        Index("idx_sync_logs_start_time", "start_time"),
    )

    def __repr__(self):
        return f"<SyncLog(id={self.id}, task_id={self.sync_task_id}, status={self.status})>"